package com.example.dao.impl;

import com.example.dao.StudentDao;
import com.example.domain.Student;
import com.example.util.JDBCUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: sms
 * @description:
 * @author: zhanghz001
 * @create: 2020-11-08 20:12
 **/
public class StudentDaoImpl implements StudentDao {
    @Override
    public List<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            //注册了驱动
            Class.forName("com.mysql.jdbc.Driver");
            //驱动管理 对象获取连接
            con = DriverManager.getConnection(
                    "jdbc:mysql://192.168.23.129:3306/db24",
                    "root", "7482386379zh");
            //根据数据库连接对象获取,执行sql的对象
            stat = con.createStatement();
            //执行查询方法,得到结果集对象
            rs = stat.executeQuery("select * from student");

            //遍历结果集对象,拿到所有的值
            while (rs.next()) {
                //
                //    sid INT PRIMARY KEY AUTO_INCREMENT, -- 学生id

                int sid = rs.getInt("sid");
                // NAME VARCHAR(20), -- 学生姓名
                String name = rs.getString("NAME");

                // age INT, -- 学生年龄
                int age = rs.getInt("age");

                // birthday DATE -- 学生生日
                Date birthday = rs.getDate("birthday");
                Student stu = new Student(sid, name, age, birthday);
                list.add(stu);
            }

            //
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭链接.执行流程,执行结果
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public Student findById(Integer id) {
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        Student student = null;
        try {
            //    加载注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            //根据驱动管理对象 获取数据库连接对象
            con = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/db24",
                    "root", "7482386379zh");
            //根据数据库连接对象获取到,执行sql语句对象
            stat = con.createStatement();
            //执行返回,结果集对象
            rs = stat.executeQuery("select * from student where sid='" + id + "'");
            if (rs.next()) {

                int sid = rs.getInt("sid");
                String name = rs.getString("NAME");
                int age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                student = new Student(sid, name, age, birthday);
            }
            //循环结果集,获取结果
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭数据库链接对象,执行sql语句对象,结果集对象
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return student;
    }

    //新增数据
    @Override
    public int insert(Student stu) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //通过驱动管理对象,获取数据库连接对象
            con = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/db24"
                    , "root", "7482386379zh");

            //数据库连接对象,获取到数据库sql执行对象
            stat = con.createStatement();
            String name = stu.getName();
            int age = stu.getAge();
            java.util.Date date = stu.getBirthday();
            //格式化日期
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(date);
            //执行对象执行sql,
            String str = "insert into student (NAME, age, birthday)\n" +
                    "values ('" +
                    name + "', "
                    + age + ", '"
                    + birthday + "');";
            System.out.println(str);
            result = stat.executeUpdate(str);
            //获取返回,结果集对象
            //遍历结果集对象

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭链接.数据库sql执行对象,结果集对象
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return result;
    }

    @Override
    public int update(Student stu) {
        int result = 0;
        Connection con = null;
        Statement stat = null;
        try {
            con = JDBCUtils.getConnection();
            stat = con.createStatement();
            //执行sql
            java.util.Date date = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(date);
            String name = stu.getName();
            int age = stu.getAge();
            int sid = stu.getSid();

            String str = "update student\n" +
                    "set NAME    = '" + name + "',\n" +
                    "    age=" + age + ",\n" +
                    "    birthday='" + birthday + " '\n" +
                    "where sid = " + sid + ";";
            result = stat.executeUpdate(str);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con, stat);
        }
        return result;
    }

    @Override
    public int delete(Integer i) {
        int result = 0;
        Connection con = null;
        Statement stat = null;
        try {
            con = JDBCUtils.getConnection();
            stat = con.createStatement();
            int sid = i;
            String str = "delete from student where  sid=" + sid + ";";
            result = stat.executeUpdate(str);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con, stat);
        }
        return result;
    }

}
